So now that we've seen how to create formulas that will span across multiple worksheets and by the way
you can also do the same thing across multiple workbooks.
Let's take a look at a more automated approach get Excel to do some of the work for us.
Same scenario here.
Got the exercise file open.
I'm looking at the summary sheet.
I deleted out the circulations that I created earlier.
Now I want to perform the same thing but utilizing a built in tool instead of Excel call consolidate
with a little bit of input from us centrally Where's the data at.
Excel will fill in the data for us.
Now I need to do a little bit of housekeeping here first on the Summary sheet.
I've got the towns and with the consolidate feature I really don't need the town listings here so I'm
going to grab cells.
4 to 8 and I'm going to hit my delete key on my keyboard.
Let's get rid of him.
The reason why I'm deleting those is the con. feature is not only going to sum up all the circulations
for me but it's also going to identify the proper town for me.
Watch this.
So I'm on the Summary sheet.
I delete a lot of the towns.
I am gonna click inside cell A4.
I'm going to go up to my data tab top of my screen towards the far right we've got a section called
data tools and I'm going to go to consolidate.
I'll give that a click.
This is a con. when no where just with a little bit of input Excel is going to consolidate the 2013
14 and 15 sheets all into the summary sheet for me.
So the first step wants to know well what type of consolidation do you want to perform here.
And I've got a number of different functions.
While for me I'm just going to do a standard sun.
Now it needs the references it needs to know where the data is.
At that you want to sum up on the summary sheets.
Well let's move this window.
I'm going to click into the reference box I'll go down to 2013 and I'm going to select cells A-4 down
to be 8.
Let's grab that as my first reference.
I'm going to go back in and hit add I've added my 2013 reference.
Now I need to do is two more times.
So I'll go down to 2014.
Make sure the same range is highlighted.
Hit the Add button.
Now I've got two of them.
Let's do it one more time 2015.
Make sure I grab the same range again and add it all right.
So I've got my three references the three separate sheets.
Now we've got some additional options here.
Three little checkmarks.
First use labels end.
Now imagine this.
You've got three different years worth of Circulations with the towns and the circulation numbers.
Well what if throughout the years you're adding and removing towns.
Some towns have closed down some towns have opened up new circulation numbers are introduced but because
we're not consistent anymore.
Excel needs a way to identify the towns as it moves from sheet to sheet so it can consolidate Beverly
with Beverly and doesn't actually do it.
Beverly in Cherry Dale together that's where the labels come into play.
Now our references that we just established have two columns.
The first column is the town or essentially the label for the circulation numbers.
So I'm going to tell it to use the left column from my references as the label.
So I'll turn that on.
The other option here create links to source data.
Now I'm not going to turn mine on or leave it up to you.
But if you check that it creates a link from your summary sheet to your previous three sheets.
This way if 2013 14 or 15 update their numbers the summary sheet will automatically update.
So again I'll leave it up to you if you want that to be linked or not.
But make sure you use left Cohen as the labels so that this consolidate feature knows the match up Beverly
with Beverly and Cherry Dale with Cherry Dale and so on.
So now all I need to do get my three references using left column.
I'm going to hit OK.
And I've now consolidated that data from the three other sheets.
Got the three million two hundred forty five thousand nine hundred ninety eight.
All from the 2013 through 15 years of Beverley and so on all of them for you.
But just a little bit of input from us.
Excel did the consolidation for us.
So try this out.
Wipe out the previous exercise get rid of those circulation numbers that you did manually and use the
consolidate feature.
Remember you got to delete the towns that you don't need those on the summary sheet then hop up to sell
A-4 go to data to consolidate grab your references.
Use the left column is your label and you're done.
It's a beautiful aid saving you tons of time that you would normally have to do manually.
It automated for you to try it out.